package com.hejjon.util;

import com.hejjon.core.bean.TableField;
import com.hejjon.core.constant.TableFieldConstants;
import com.hejjon.core.exception.BusinessException;
import com.hejjon.core.utils.DateTimeUtil;
import com.hejjon.domain.bo.RunInsertSqlBO;
import com.hejjon.domain.bo.RunQuerySqlBO;
import com.hejjon.domain.bo.RunUpdateSqlBO;
import com.hejjon.domain.pojo.ConnectionInfo;
import com.hejjon.domain.pojo.FilterCondition;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.Date;

/**
 * @author: cs
 * @date: 2025-02-21 17:27:12
 * @since: 1.0
 */
public class JDBCUtil {

    private static final Logger log = LoggerFactory.getLogger(JDBCUtil.class);

    private static final String USE_PREFIX = "USE ";

    /**
     * 获取 JDBC 数据库连接
     */
    private static Connection takeJdbcConnection(ConnectionInfo connInfo) {

        Connection conn = null;
        String driver = connInfo.getDriver();
        String url = connInfo.getUrl();
        String username = connInfo.getUsername();
        String password = connInfo.getPassword();

        try {
            // 加载驱动
            Class.forName(driver);
            // 获取连接
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {

            throw new BusinessException("获取数据库连接出现异常, 连接信息: \n" + connInfo.toJson() + "\n" + ",异常信息: \n" + e.getMessage());
        }
        return conn;
    }


    /**
     *
     */
    public static Connection getConnection(ConnectionInfo connInfo) {
        try {
            return takeJdbcConnection(connInfo);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    private static void logSQL(String sql) {
        System.out.println("================================================= 开始执行SQL =================================================");
        System.out.println(sql);
        System.out.println("================================================= 以上执行SQL =================================================");
    }


    /**
     * 执行语句
     * 包括: 新建, 删除库表, 插入表数据, 更新表数据 操作
     */
    public static boolean executeSQL(Connection connection, String ddlSQL) {
        try {
            logSQL(ddlSQL);
            boolean autoCommit = connection.getAutoCommit();        // 保存原始的自动提交模式
            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            connection.setAutoCommit(false);
            statement.executeUpdate(ddlSQL);
            connection.commit();
            connection.setAutoCommit(autoCommit);
            log.info("SQL语句: {} 执行成功", ddlSQL);
            return true;
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new BusinessException("执行SQL出现异常: " + ex.getMessage());
            }
            throw new BusinessException("执行SQL出现异常: " + e.getMessage());
        }
    }



    public static boolean batchExecuteSQL(Connection connection, List<String> sqlList) {

        try {
            log.info("======== 开始批量执行sql =========");
            boolean autoCommit = connection.getAutoCommit();        // 保存原始的自动提交模式
            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            connection.setAutoCommit(false);
            for (String sql : sqlList) {
                statement.addBatch(sql);
            }
            statement.executeBatch();
            connection.commit();
            connection.setAutoCommit(autoCommit);   // 恢复原始的自动提交模式
            return true;
        } catch (SQLException e) {
            try {
                log.error("执行sql出错");
                connection.rollback();
                return false;
            } catch (SQLException ex) {
                log.error("batchExecuteSQL: 回滚失败!");
                throw new RuntimeException(ex);
            }
        }
    }


    public static int executeInsert(RunInsertSqlBO insertSqlBO) {

        Connection connection = insertSqlBO.getConnection();
        String databaseName = insertSqlBO.getDatabaseName();
        String sql = insertSqlBO.getSqlScript();
        List<TableField> fieldList = insertSqlBO.getFieldList();
        List<Map<String, Object>> valueList = insertSqlBO.getValueList();

        // 切换数据库
        executeSQL(connection, USE_PREFIX + databaseName);
        logSQL(sql);
        PreparedStatement ps = null;
        try {
            boolean autoCommit = connection.getAutoCommit();        // 保存原始的自动提交模式
            ps = connection.prepareStatement(sql);
            connection.setAutoCommit(false);
            int cnt = 1;
            for (Map<String, Object> valueMap : valueList) {
                for (TableField field : fieldList) {
                    Object fieldValue = valueMap.get(field.getName());
                    ps.setObject(cnt, fieldValue);
                    cnt++;
                }
            }
            ps.execute();
            connection.commit();
            connection.setAutoCommit(autoCommit);   // 恢复原始的自动提交模式
            log.info(">>>>>>>>>>>>>>>>>>>> sql语句执行完成, 成功插入 {} 条数据. >>>>>>>>>>>>>>>>>>>", valueList.size());
        } catch (SQLException e) {
            log.error("执行插入语句失败, 异常信息: {}", e.getMessage());
            try {
                connection.rollback();
            } catch (SQLException ex) {
                log.error("回滚失败!");
                throw new BusinessException("回滚失败!" + ex.getMessage());
            }
            throw new BusinessException("执行插入语句失败, 异常信息: " + e.getMessage());
        }

        return valueList.size();
    }


    public static List<Map<String, Object>> executeQuery(RunQuerySqlBO querySqlBO) {
        String databaseName = querySqlBO.getDatabaseName();
        Connection connection = querySqlBO.getConnection();
        List<FilterCondition> conditionList = querySqlBO.getConditionList();
        String sql = querySqlBO.getSqlScript();
        List<TableField> queryFieldList = querySqlBO.getQueryFieldList();

        // 切换数据库
        executeSQL(connection, USE_PREFIX + databaseName);
        logSQL(sql);
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            for (int i = 0; i < conditionList.size(); i++) {
                Object paramObj = conditionList.get(i).getParamValue();
                ps.setObject(i + 1, paramObj);
            }
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Map<String, Object> rowMap = new HashMap<>();
                for (TableField field : queryFieldList) {
                    Object fieldValue = readFieldValue(field, resultSet);
                    rowMap.put(field.getName(), fieldValue);
                }
                result.add(rowMap);
            }

        } catch (SQLException e) {
            throw new BusinessException("SQL执行失败,异常信息: " + e.getMessage());
        }
        return result;
    }

    /**
     * 读取字段值
     */
    private static Object readFieldValue(TableField field, ResultSet resultSet) {
        Object fieldValue = null;
        String fieldName = field.getName();
        try {
            fieldValue = resultSet.getObject(fieldName);
            if (field.getIsDate()) {
                if (fieldValue instanceof Date) {
                    fieldValue = DateTimeUtil.date2Str((Date) fieldValue);
                } else if (fieldValue instanceof LocalDate) {
                    fieldValue = DateTimeUtil.date2Str((LocalDate) fieldValue);
                }
            }
            if (field.getIsDatetime()) {
                if (fieldValue instanceof Date) {
                    fieldValue = DateTimeUtil.datetime2Str(((Date) fieldValue));
                } else if (fieldValue instanceof LocalDateTime) {
                    fieldValue = DateTimeUtil.datetime2Str((LocalDateTime) fieldValue);
                }
            }
        } catch (SQLException e) {
            throw new BusinessException("读取字段值出现异常, 字段名称: " + fieldName);
        }
        return fieldValue;
    }

    /**
     * 执行更新语句
     */
    public static int executeUpdate(RunUpdateSqlBO updateSqlBO) {

        String databaseName = updateSqlBO.getDatabaseName();
        Connection connection = updateSqlBO.getConnection();

        // 切换数据库
        executeSQL(connection, USE_PREFIX + databaseName);
        // 打印sql
        logSQL(updateSqlBO.getSqlScript());
        String sql = updateSqlBO.getSqlScript();
        List<Map<String, Object>> valueList = updateSqlBO.getValueList();
        List<TableField> fieldList = updateSqlBO.getUpdateFieldList();
        PreparedStatement ps = null;
        try {
            boolean autoCommit = connection.getAutoCommit();        // 保存原始的自动提交模式
            connection.setAutoCommit(false);
            ps = connection.prepareStatement(sql);
            for (Map<String, Object> valueMap : valueList) {
                int cnt = 1;
                for (TableField field : fieldList) {
                    Object fieldValue = valueMap.get(field.getName());
                    ps.setObject(cnt, fieldValue);
                    cnt++;
                }
                ps.setObject(cnt, valueMap.get(TableFieldConstants.FIELD_ID).toString());           // id条件
                ps.addBatch();      // 增加
            }
            ps.executeBatch();
            connection.commit();
            connection.setAutoCommit(autoCommit);   // 恢复原始的自动提交模式
            log.info(">>>>>>>>>>>>>>>>>>>> sql语句执行完成, 成功更新 {} 条数据. >>>>>>>>>>>>>>>>>>>", valueList.size());
            return valueList.size();
        } catch (SQLException e) {
            log.error("执行更新语句失败, 异常信息: {}", e.getMessage());
            try {
                connection.rollback();
            } catch (SQLException ex) {
                log.error("回滚失败!");
                throw new BusinessException("回滚失败!" + ex.getMessage());
            }
            throw new BusinessException("执行更新语句失败, 异常信息: " + e.getMessage());
        }
    }



}
